#Region "Comment"

'  Module                       :    Datalayer's Core Module
'  Purpose                      :    Provide methods to run on database
'  Tables Reffered              :    N/A
'  Tables Modified              :    N/A
'  Chronological Development

#End Region

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Diagnostics
Imports System.Collections
Imports System.IO
Imports System.Xml
Imports System

Namespace DataLayer

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Return type expected after execution result.
    ''' </summary>
    ''' <remarks>
    ''' To specify the return type expected after execution of method.
    ''' If it is mentioned DataSet then the returned object will contain dataset.
    ''' </remarks>
    ''' <history>
    ''' </history>
    ''' -----------------------------------------------------------------------------
    ''' 

    Public Enum ReturnTypes
        Scalar
        NonQuery
        DataReader
        DataSet
        XMLReader
    End Enum

    ''' -----------------------------------------------------------------------------
    ''' Project	 : CASMain
    ''' Struct	 : DataLayer.ExecutionResult
    ''' 
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Structure that contains execution result of the query along with a set of parameters passed for execution.
    ''' </summary>
    ''' <remarks>
    ''' Structure that contains execution result of the query along with a set of parameters passed for execution.
    ''' Returned structure contains two objects.
    ''' 1. Result Object - Containing the execution result object.
    ''' 2. Result Parameters - An array of parameters that were passed for execution; to get the value of output parameters.
    ''' </remarks>
    ''' <history>
    ''' 	[anuradhan]	11/07/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    ''' 

    Public Structure ExecutionResult
        Public ResultObject As Object
        Public ResultParamenters() As SqlParameter
        Public ex As SqlException
        Public strMsg As String
        Public blnIsError As Boolean
    End Structure

    ''' -----------------------------------------------------------------------------
    ''' Project	 : CASMain
    ''' Class	 : DataLayer.CoreFunctions
    ''' 
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Contains core functions related to database connection.
    ''' </summary>
    ''' <remarks>
    ''' Contains core functions related to database connection.
    ''' Like getting connection string, getting connection object and execution method.
    ''' </remarks>
    ''' <history>
    ''' 	[anuradhan]	11/07/2006	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    ''' 

    Public Class CoreFunctions

        'Public Shared Function GetDatabaseConnectionString() As String
        '    Return System.Configuration.ConfigurationManager.AppSettings("connectionString").Trim

        'End Function

        ''' -----------------------------------------------------------------------------
        ''' <summary>
        ''' Gets the connection object from the connection string specified in configuration file.
        ''' </summary>
        ''' <returns>An instance of connection object.</returns>
        ''' <remarks>
        ''' Gets the connection object from the connection string specified in configuration file.
        ''' Connection needs to be opened before working on that.
        ''' </remarks>
        ''' <history>
        ''' 	[anuradhan]	11/07/2006	Created
        ''' </history>
        ''' -----------------------------------------------------------------------------

        Public Shared Function GetDatabaseConnection(ByVal strConnectionString As String) As SqlConnection
            Dim objConnection As SqlConnection
            Try
                objConnection = New SqlConnection(strConnectionString)
                objConnection.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("SqlServer").ToString()

            Catch ex As Exception
                Throw ex
            End Try
            Return (objConnection)
        End Function

        ''' -----------------------------------------------------------------------------
        ''' <summary>
        ''' Common method to execute a set of queries on database configured in configuration file.
        ''' </summary>
        ''' <param name="CommandText">Transact-SQL statement or stored procedure to execute at the data source.</param>
        ''' <param name="CommandType">Value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted</param>
        ''' <param name="ReturnType">Value indicating how the result should be returned after execution</param>
        ''' <param name="Parameters">Input/Output parameters required to execute the commend</param>
        ''' <returns>Structure that contains execution result of the query along with a set of parameters passed for execution.</returns>
        ''' <remarks>
        ''' Common method to execute a set of queries on database configured in configuration file.
        ''' </remarks>
        ''' <history>
        ''' 	[anuradhan]	11/07/2006	Created
        ''' </history>
        ''' -----------------------------------------------------------------------------

        Public Shared Function Execute(ByVal strConnectionString As String, ByVal CommandText As String, ByVal CommandType As CommandType, ByVal ReturnType As ReturnTypes, Optional ByVal Parameters() As SqlClient.SqlParameter = Nothing) As ExecutionResult
            ' Dim objConnection As New SqlConnection(GetDatabaseConnectionString)


            Dim objConnection = GetDatabaseConnection(strConnectionString)
            Dim objCommand As New SqlCommand(CommandText, objConnection)
            Dim objExecutionResult As New ExecutionResult
            Dim blnCloseConnection As Boolean = True

            objCommand.CommandType = CommandType
            objCommand.CommandTimeout = 0
            'Adds Parameter To Command Object If Passed To Method
            If Not (Parameters Is Nothing) Then
                For i As Integer = 0 To UBound(Parameters)
                    objCommand.Parameters.Add(Parameters(i))
                Next
            End If
           
            Try
                objConnection.Open()
                Select Case ReturnType
                    Case ReturnTypes.NonQuery
                        objExecutionResult.ResultObject = objCommand.ExecuteNonQuery
                    Case ReturnTypes.Scalar
                        objExecutionResult.ResultObject = objCommand.ExecuteScalar
                    Case ReturnTypes.DataReader
                        Dim objDR As SqlDataReader
                        objDR = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
                        objExecutionResult.ResultObject = objDR
                        blnCloseConnection = False
                    Case ReturnTypes.DataSet
                        Dim objDA As New SqlDataAdapter(objCommand)
                        Dim objDS As New DataSet
                        objDA.Fill(objDS)
                        objDA.Dispose()
                        objDA = Nothing
                        objExecutionResult.ResultObject = objDS
                    Case ReturnTypes.XMLReader
                        objExecutionResult.ResultObject = objCommand.ExecuteXmlReader
                End Select
                objExecutionResult.ResultParamenters = Parameters
            Catch ex As SqlException
                objExecutionResult.ex = ex
                If Not (ex.Number = 2627 OrElse ex.Number = 547) Then Throw ex
            Finally
                If Not (objCommand Is Nothing) Then
                    objCommand.Dispose()
                    objCommand = Nothing
                End If

                If blnCloseConnection Then
                    If Not (objConnection Is Nothing) Then
                        If (objConnection.State <> ConnectionState.Closed) Then
                            objConnection.Close()
                        End If
                        objConnection.Dispose()
                        objConnection = Nothing
                    End If
                End If
            End Try
            Return (objExecutionResult)
        End Function

        ''' -----------------------------------------------------------------------------
        ''' <summary>
        ''' Common method to execute a set of queries within a Transaction on database configured in configuration file.
        ''' </summary>
        ''' <param name="Transaction">Instance of a SQL-Transaction object to participate in transaction.</param>
        ''' <param name="CommandText">Transact-SQL statement or stored procedure to execute at the data source.</param>
        ''' <param name="CommandType">Value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted</param>
        ''' <param name="ReturnType">Value indicating how the result should be returned after execution</param>
        ''' <param name="Parameters">Input/Output parameters required to execute the commend</param>
        ''' <returns>Structure that contains execution result of the query along with a set of parameters passed for execution.</returns>
        ''' <remarks>
        ''' Common method to execute a set of queries within a Transaction on database configured in configuration file.
        ''' </remarks>
        ''' <history>
        ''' 	[anuradhan]	11/07/2006	Created
        ''' </history>
        ''' -----------------------------------------------------------------------------

        Public Shared Function Execute(ByVal Transaction As SqlTransaction, ByVal CommandText As String, ByVal CommandType As CommandType, ByVal ReturnType As ReturnTypes, Optional ByVal Parameters() As SqlClient.SqlParameter = Nothing) As ExecutionResult
            Dim objExecutionResult As New ExecutionResult
            Dim objCommand As SqlCommand

            objCommand = Transaction.Connection.CreateCommand()
            objCommand.Transaction = Transaction
            objCommand.CommandText = CommandText
            objCommand.CommandType = CommandType
            objCommand.CommandTimeout = 0
            'Adds Parameter To Command Object If Passed To Method
            If Not (Parameters Is Nothing) Then
                For i As Integer = 0 To UBound(Parameters)
                    objCommand.Parameters.Add(Parameters(i))
                Next
            End If

            Try
                Select Case ReturnType
                    Case ReturnTypes.NonQuery
                        objExecutionResult.ResultObject = objCommand.ExecuteNonQuery
                    Case ReturnTypes.Scalar
                        objExecutionResult.ResultObject = objCommand.ExecuteScalar
                    Case ReturnTypes.DataReader
                        Dim objDR As SqlDataReader
                        objDR = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
                        objExecutionResult.ResultObject = objDR
                    Case ReturnTypes.DataSet
                        Dim objDA As New SqlDataAdapter(objCommand)
                        Dim objDS As New DataSet
                        objDA.Fill(objDS)
                        objDA.Dispose()
                        objDA = Nothing
                        objExecutionResult.ResultObject = objDS
                    Case ReturnTypes.XMLReader
                        objExecutionResult.ResultObject = objCommand.ExecuteXmlReader
                End Select
                objExecutionResult.ResultParamenters = Parameters
                Return (objExecutionResult)
            Catch ex As SqlException
                Throw ex
            Finally
                If Not (objCommand Is Nothing) Then
                    objCommand.Dispose()
                    objCommand = Nothing
                End If
            End Try
        End Function

    End Class
End Namespace
